package dbOperator;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
*这是一个测试类，用于测试函数而不用于系统，里面的所有函数都可以在advancedOperation.java找到
*
*/
public class myOP {
	/**
	 * 这个函数的作用是根据用户的ID号确定其身份为 读者、图书管理员还是超级管理员，其中 1是超级管理员，2是图书管理员，3是读者，返回值为0表示当前用户不存在
	 * 
	 * @param uid 用户的ID号
	 * @return identity 用户的身份代号
	 * @throws SQLException 数据库查询语句执行错误时会抛出此异常
	 */
	public static int getIdentity(int uid) throws SQLException {
		Connection con = BasicOperation.getConnection();
		ResultSet rs = BasicOperation.getQueryResult(con, "select identity from User where uid = " + uid);
		if (rs.next()) {
			int identity = rs.getInt(1);
			con.close();
			return identity;
		}
		return 0;
	}

	/**
	 * 这个函数的作用是根据用户的ID号确定其身份为 读者、图书管理员还是超级管理员，其中 1是超级管理员，2是图书管理员，3是读者，返回值为0表示当前用户不存在
	 * 
	 * @param con 建立的数据库连接
	 * @param uid 用户的ID号
	 * @return identity 用户的身份代号
	 * @throws SQLException 数据库查询语句执行错误时会抛出此异常
	 */
	public static int getIdentity(Connection con, int uid) throws SQLException {
		ResultSet rs = BasicOperation.getQueryResult(con, "select identity from User where uid = " + uid);
		if (rs.next()) {
			int identity = rs.getInt(1);
			return identity;
		}
		return 0;
	}

	// 以下函数的作者是胡兰氪
	/**
	 * 这个函数的作用是提供bid和uid以便预约书
	 * 
	 * 流程：
	 *  1.查询这本书是否已经被这个读者预约了，如果是，则禁止重复预约，并返回
	 *  2.将这本书加入预约列表，过期时间根据当前时间和数据库取得的参数设定，状态置为"reserving"
	 *  3.此操作对SpecificBook无任何影响
	 * 
	 * @param con 数据库链接
	 * @param bid 书本的ID号
	 * @param readerUid 读者的uid
	 * @return 返回预约的bookID提供给预约功能，如果已经预约过一本或者没有可供预约的书，则返回空字符串(预约不应该针对具体的一本书，还要继续改)
	 * @throws SQLException
	 * @throws ParseException 
	 */
	public static int getBookReserved(Connection con,int bid,int readerUid) throws SQLException, ParseException{
		//身份验证
		if(getIdentity(con,readerUid)!=3) return -1;
		//同一本书不能多次预约
		getExpiredReserveInvalidate(con, bid);
		String queryScript = "select * from ReserveList where uid = " + readerUid + " and bid = " + bid + " and status = 'reserving'";
		ResultSet rs = BasicOperation.getQueryResult(con,queryScript);
		if(rs.next()) return -1;
		String sql="select reserveMaintainingHours from SystemAttribute";
		ResultSet res=BasicOperation.getQueryResult(con, sql);
		if(!res.next())return -1;
		int reserveMaintainingHours=res.getInt("reserveMaintainingHours");
		String updateScript = "insert into ReserveList values(" + readerUid + "," + bid + ",'" 
					+ dataOperator.BasicOperation.getDateWithTimeAfterHours(reserveMaintainingHours) + "','reserving')";
		return BasicOperation.executeUpdateScript(con, updateScript);
	}
	
	/**
	 * 将所有当前时间超过expireDate的ReserveList项的状态置为"expired"
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ParseException 
	 */
	private static int getExpiredReserveInvalidate(Connection con, int bid) throws SQLException, ParseException {
		String getList = "select expirationDate from ReserveList where bid='" + bid+"'and status='reserving'";
		ResultSet List = BasicOperation.getQueryResult(con, getList);
		long recentTime = System.currentTimeMillis();
		int n = 0;
		String sql = null;
		Date date = null;
		String formatdate=null;
		while (List.next()) {
			formatdate=List.getString("expirationDate");
			date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(formatdate);
			if (date.getTime() < recentTime) {
				sql = "update ReserveList set status = 'expired' where bid = '" + bid + "' and expirationDate = '" + formatdate+"'";
				BasicOperation.executeUpdateScript(con, sql);
				n++;
			}
		}
		return n;
	}
	/**
	 * 
	 * 这个函数的作用是更新借书列表，如果指定的书本已经在列表中且存在借出状态，则将其更新为已归还，或禁止再次借出
	 * @param bookID 书本的bookID
	 * @param readerUid 读者的uid
	 * @param status 借书列表中书本的状态，取值只能为borrowed(已借出) returned(已归还)，当书本已归还时状态内要追加归还日期，同时更新SpecificBook中的书本状态
	 * @throws SQLException
	 * 
	 */
	public static int updateBorrowList(Connection con,String bookID,int librarianUid,int readerUid,String status) throws SQLException{
	    if((getIdentity(con,librarianUid)!=2)||getIdentity(con,readerUid)!=3) return -1;
	    if(!(status.equals("returned")||status.equals("borrowed"))) return -1;
	    if(status.equals("borrowed")){
	        String queryScript = "select * from BorrowList where bookID = '" + bookID + "' and status = 'borrowed'";
	        ResultSet rs = BasicOperation.getQueryResult(con,queryScript);
	      //不能将已经借出的书再次借出
	        if(rs.next()) return -1;
	        queryScript="select bid from SpecificBook where bookID ='"+bookID+"'";
	        rs=BasicOperation.getQueryResult(con, queryScript);
	        int bid=0;
	        if(rs.next())bid=rs.getInt(1);
	        String updateScript = "insert into BorrowList values ('" + readerUid + "','" + bid + "','"+ bookID + "','" + dataOperator.BasicOperation.getDateWithTimeInString() + "','borrowed')";
	        return BasicOperation.executeUpdateScript(con, updateScript);
	    }
	    if(status.equals("returned")) {
	    	String updateScript = "update BorrowList set status = 'returned (Date = " + dataOperator.BasicOperation.getDateWithTimeInString() + ")' where bookID = '" + bookID + "' and uid = " + readerUid;
	    	updateBookStatus(con, bookID, librarianUid, readerUid, "available");
	    	return BasicOperation.executeUpdateScript(con, updateScript);
	    }
	    return -1;
	}
	/**
	 *  这个函数的作用是更新该书目状态，如果是更新为借出状态函数会进一步更新borrowList
	 * @param con 数据库链接
	 * @param bookID 书目的bookID
	 * @param librarianUid 图书管理员的uid，不要填为定值
	 * @param readerUid 读者的uid，不要填为定值
	 * @param status 书本当前状态，取值应该为 available(在馆) borrowed(已借出)
	 * @throws SQLException
	 */
	public static int updateBookStatus(Connection con,String bookID,int librarianUid,int readerUid,String status) throws SQLException{
		//身份验证
		if((getIdentity(con,librarianUid)!=2)||(getIdentity(con,readerUid)!=3)) return -1;
		//字符串取值检查
		if(!(status.equals("available")||status.equals("borrowed"))) return -1;
		String updateScript="update SpecificBook set status = '" + status + "' where bookID ='" + bookID +"'";
		BasicOperation.executeUpdateScript(con,updateScript);
		if(status.equals("borrowed")){
		    return updateBorrowList(con, bookID, librarianUid, readerUid, status);
		}
		return -1;
	}
	/**
	 * 这个函数的作用是将读者预约的书借出，必须由图书管理员调用 流程： 1.判定两人的身份是否正确，如果不正确直接返回
	 * 2.调用getExpiredReserveInvalidate()，将过期的预约记录全部置为"expired"
	 * 3.查询是否有【正在预约"reserving"】的预约记录，如果有，管理员找出一本状态为"available"的书借给读者，调用updateBookStatus(...)将状态置为"borrowed"，同时将本预约记录的状态改为"borrowed"
	 * 
	 * @param con
	 * @param bid
	 * @param librarianUid
	 * @param readerUid
	 * @return
	 * @throws SQLException
	 * @throws ParseException 
	 */
	public static boolean getReservedBookBorrowed(Connection con, int bid, int librarianUid,int readerUid )
			throws SQLException, ParseException {
		int identity = getIdentity(con, librarianUid);
		if (identity != 2)
			return false;
		getExpiredReserveInvalidate(con, bid);
		String sql = "select * from ReserveList where uid='"+readerUid+"'and bid='"+bid+"' and status='reserving'";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		if(!res.next())return getBookBorrowedDirectly(con, bid, librarianUid, readerUid);
		sql="update ReserveList set status='borrowed' where uid='"+readerUid+"'and bid='"+bid+"' and status ='reserving'";
		BasicOperation.executeUpdateScript(con, sql);
		sql = "select bookID from SpecificBook where status='available' and bid='"+bid+"'";
		res = BasicOperation.getQueryResult(con, sql);
		String bookID = null;
		if (!res.next())return false;
			bookID = res.getString(1);
		updateBookStatus(con, bookID, librarianUid, readerUid, "borrowed");
		return true;
	}
	/**
	 * 这个函数的作用是直接将书借出，必须由图书管理员调用 流程： 1.判定两人的身份是否正确，如果不正确直接返回
	 * 2.调用getExpiredReserveInvalidate()，将过期的预约记录全部置为"expired"
	 * 3.查询排除被预约的状态为"available"书，如果有，管理员找出一本借给读者，调用updateBookStatus(...)将状态置为"borrowed"
	 * 
	 * @param con
	 * @param bid
	 * @param librarianUid
	 * @param readerUid
	 * @return
	 * @throws SQLException
	 * @throws ParseException 
	 */
	public static boolean getBookBorrowedDirectly(Connection con, int bid, int librarianUid,int readerUid )
			throws SQLException, ParseException {
		int identity = getIdentity(con, librarianUid);
		if (identity != 2)
			return false;
		getExpiredReserveInvalidate(con, bid);
		String sql = "select count(*) from ReserveList where bid='"+bid+"'and status ='reserving'";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		res.next();
		int n=res.getInt(1);
		sql = "select bookID from SpecificBook where status='available' and bid='"+bid+"'";
		res = BasicOperation.getQueryResult(con, sql);
		res.last();
		int m=res.getRow();
		System.out.println(m);
		if(m<=n)return false;
		res.beforeFirst();
		String bookID = null;
		if (!res.next())return false;
			bookID = res.getString(1);
		updateBookStatus(con, bookID, librarianUid, readerUid, "borrowed");
		return true;
	}
	// 以下为测试方法
	// 查看书类
	public static void showBook() throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "select * from Book";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		while (res.next()) {
			System.out.println(
					"bid:" + res.getInt("bid") + " name:" + res.getString("name") + " ISBN:" + res.getString("ISBN"));
		}
		con.close();
	}

	// 查看副本
	public static void showSpecificBook() throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "select * from SpecificBook";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		while (res.next()) {
			System.out.println("bid:" + res.getInt("bid") + " bookID:" + res.getString("bookID")+" status:"+res.getString("status"));
		}
		con.close();
	}
	// 查看预约
	public static void showReserveList()throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "select * from ReserveList";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		while (res.next()) {
			System.out.println("uid:" + res.getInt("uid")+"bid:" + res.getInt("bid")+"expirationDate:"+res.getString("expirationDate")+"status:"+res.getString("status"));
		}
		con.close();
	}
	//查看借书清单
	public static void showBorrowList()throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "select * from BorrowList";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		while (res.next()) {
			System.out.println("uid:" + res.getInt("uid")+"bookID:" + res.getString("bookID")+"borrowDate:"+res.getString("borrowDate")+"status:"+res.getString("status"));
		}
		con.close();
	}
	//清空预约,借书,删除清单
	public static void doclean0() throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "truncate table ReserveList";
		BasicOperation.executeUpdateScript(con, sql);
		con.close();
	}
	public static void doclean1() throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "truncate table BorrowList";
		BasicOperation.executeUpdateScript(con, sql);
		con.close();
	}
	public static void doclean2() throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "truncate table DeleteList";
		BasicOperation.executeUpdateScript(con, sql);
		con.close();
	}
	//重置图书状态
	public static void resetBook(Connection con) throws SQLException {
		String sql="select bookID from SpecificBook";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		String bookid=null;
		while(res.next()) {
			bookid=res.getString(1);
			sql="update SpecificBook set status = 'available' where bookID='"+bookid+"'";
			BasicOperation.executeUpdateScript(con, sql);
		}
	}
	//查看用户
	public static void showUser()throws SQLException {
		Connection con = BasicOperation.getConnection();
		String sql = "select * from User";
		ResultSet res = BasicOperation.getQueryResult(con, sql);
		while (res.next()) {
			System.out.println("uid:" + res.getInt("uid")+" phoneNumber:"+res.getString("phoneNumber")+" password:"+res.getString("password")+" recentCookie:"+res.getString("recentCookie"));
		}
		con.close();
	}
	//查看删除列表
		public static void showDeleteList()throws SQLException {
			Connection con = BasicOperation.getConnection();
			String sql = "select * from DeleteList";
			ResultSet res = BasicOperation.getQueryResult(con, sql);
			while (res.next()) {
				System.out.println("uid:" + res.getInt("uid")+" bid:"+res.getInt("bid")+" date:"+res.getString("date")+" reason:"+res.getString("reason"));
			}
			con.close();
		}
	public static void main(String[] args) throws SQLException, ParseException {
		//AdvancedOperation.addBooksBybid(8,2,10);
		//showDeleteList();
		//showBook();
		//showUser();
		//Connection con=BasicOperation.getConnection();
		//resetBook(con);
		//doclean0();
		//doclean1();
		//doclean2();
		//getBookReserved(con,2, 4);
		//getExpiredReserveInvalidate(con,2);
		//getReservedBookBorrowed(con,2,8,3);
		//getBookBorrowedDirectly(con, 2, 5, 4);
		//con.close();
		//showReserveList();
		//showBorrowList();
		//showSpecificBook();
		//System.out.println(getIdentity(3));
		dbOperator.AdvancedOperation.registerNewUser("302","302", 2, 3);
	}
}
